
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sprXacNhanCongNo
	@AccountingObjectID UNIQUEIDENTIFIER
	,@Month INT
	,@Year INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


DECLARE @CongNoTonDauKy MONEY
DECLARE @PhatSinhTangTrongKyTuBanHang MONEY
DECLARE @ThanhToanTrongKy MONEY
DECLARE @PhatSinhNoLuyKeNam MONEY
DECLARE @TongPhatSinhTang MONEY
DECLARE @TongPhatSinhGiam MONEY

--Lay so du dau ky
SELECT @CongNoTonDauKy = ISNULL(SUM(GL.DebitAmount) ,0)- ISNULL(SUM(GL.CreditAmount) ,0)
FROM ATAHANOI..GeneralLedger AS GL
INNER JOIN ATAHANOI..Account AS ACC ON GL.AccountNumber = ACC.AccountNumber
WHERE (ACC.DetailByAccountingObject=1)
AND (ACC.AccountingObjectType=1)
AND GL.AccountingObjectID = @AccountingObjectID
AND DATEPART(month, GL.PostedDate) < @Month AND DATEPART(YEAR, GL.POstedDate) <= @Year
 

--Lay phat sinh tang va phat sinh giam trong ky

SELECT @TongPhatSinhTang = ISNULL(SUM(GL.DebitAmount) ,0)

,@TongPhatSinhGiam = ISNULL(SUM(GL.CreditAmount) ,0)

FROM ATAHANOI..GeneralLedger AS GL

INNER JOIN ATAHANOI..Account AS ACC ON GL.AccountNumber = ACC.AccountNumber

WHERE (ACC.DetailByAccountingObject=1)

AND (ACC.AccountingObjectType=1)

AND GL.AccountingObjectID =   @AccountingObjectID

AND DATEPART(month, GL.PostedDate) = @Month
AND DATEPART(year, GL.PostedDate) = @Year

 

 

--Lay phat sinh tang trong ky tu ban hang

SELECT @PhatSinhTangTrongKyTuBanHang = ISNULL(SUM(GL.DebitAmount) ,0)

FROM ATAHANOI..GeneralLedger AS GL

INNER JOIN ATAHANOI..Account AS ACC ON GL.AccountNumber = ACC.AccountNumber

WHERE (ACC.DetailByAccountingObject=1)

AND (ACC.AccountingObjectType=1)

AND GL.AccountingObjectID = @AccountingObjectID

AND DATEPART(month, GL.PostedDate) = @Month
AND DATEPART(year, GL.PostedDate) = @Year

AND (

GL.CorrespondingAccountNumber LIKE '511%' 

OR GL.CorrespondingAccountNumber LIKE '512%'

OR GL.CorrespondingAccountNumber LIKE '33311%'

)

 

 

--Lay thanh toan trong ky

SELECT @ThanhToanTrongKy = ISNULL(SUM(GL.CreditAmount) ,0)
FROM ATAHANOI..GeneralLedger AS GL
INNER JOIN ATAHANOI..Account AS ACC ON GL.AccountNumber = ACC.AccountNumber
WHERE (ACC.DetailByAccountingObject=1)
AND (ACC.AccountingObjectType=1)

AND GL.AccountingObjectID = @AccountingObjectID

AND DATEPART(month, GL.PostedDate) = @Month
AND DATEPART(year, GL.PostedDate) = @Year

AND (

GL.CorrespondingAccountNumber LIKE '111%'

OR GL.CorrespondingAccountNumber LIKE '112%'

OR GL.CorrespondingAccountNumber LIKE '331%'

OR GL.CorrespondingAccountNumber LIKE '311%' 

OR GL.CorrespondingAccountNumber LIKE '3388%'
)

 

--Lay phat sinh no luy ke nam

SELECT @PhatSinhNoLuyKeNam = ISNULL(SUM(GL.DebitAmount) ,0)
FROM ATAHANOI..GeneralLedger AS GL
INNER JOIN ATAHANOI..Account AS ACC ON GL.AccountNumber = ACC.AccountNumber
WHERE (ACC.DetailByAccountingObject=1)
AND (ACC.AccountingObjectType=1)
AND GL.AccountingObjectID = @AccountingObjectID
AND DATEPART (year, GL.PostedDate) = @Year
AND DATEPART(month, GL.PostedDate) <= @Month


SELECT [AccountingObjectID]
      ,[AccountingObjectCode]
      ,[AccountingObjectName]
      ,[Address]
      ,[Tel]
	,@CongNoTonDauKy AS CongNoTonDauKy
	,@PhatSinhTangTrongKyTuBanHang AS PhatSinhTangTrongKyTuBanHang
	,@ThanhToanTrongKy AS ThanhToanTrongKy
	,@PhatSinhNoLuyKeNam AS PhatSinhNoLuyKeNam
	,@TongPhatSinhTang AS TongPhatSinhTang
	,@TongPhatSinhGiam AS TongPhatSinhGiam
  FROM [ATAHANOI].[dbo].[AccountingObject]
where AccountingObjectID = @AccountingObjectID
END
GO
